﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;
using HRM.DAL.Report;

namespace HRM.DAL.Gateway
{
    public class EvaluationGateway : DBGateway
    {
        public bool SaveEvaluation(int empId, int pmId, Evaluation evaluation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblEvaluation values(@emp,@pm,@date,@skill,@communication,@punctuality,@comments)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@emp", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@pm", SqlDbType.Int).Value = pmId;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = evaluation.Date;
                CommandObj.Parameters.Add("@skill", SqlDbType.VarChar).Value = evaluation.TechnicalSkill;
                CommandObj.Parameters.Add("@communication", SqlDbType.VarChar).Value = evaluation.Communication;
                CommandObj.Parameters.Add("@punctuality", SqlDbType.VarChar).Value = evaluation.Punctuality;
                CommandObj.Parameters.Add("@comments", SqlDbType.VarChar).Value = evaluation.Comments;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Saving Information, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool EditEvaluation(int empId, Evaluation evaluation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblEvaluation set date=@date,Skill=@skill,Communication=@communication,Punctuality=@punctuality,Comments@comments where EmpId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = evaluation.Date;
                CommandObj.Parameters.Add("@skill", SqlDbType.VarChar).Value = evaluation.TechnicalSkill;
                CommandObj.Parameters.Add("@communication", SqlDbType.VarChar).Value = evaluation.Communication;
                CommandObj.Parameters.Add("@punctuality", SqlDbType.VarChar).Value = evaluation.Punctuality;
                CommandObj.Parameters.Add("@comments", SqlDbType.VarChar).Value = evaluation.Comments;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Updating Information, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public SingleEmployeEvaluationReport GetEmployeesEvaluation(Code code)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select tblEmployees.Name,tblDesignations.Title,tblProjects.ProjectName,tblProjectManager.EmpId as manager,
                                           tblEvaluation.date,tblEvaluation.Skill,tblEvaluation.Communication,tblEvaluation.Punctuality,tblEvaluation.Comments
                                           
                                           from tblCode inner JOIN tblEmployees
                                           on tblCode.Id = tblEmployees.Code_CodeID inner JOIN tblDesignations
                                           on tblEmployees.DesignationID=tblDesignations.Id inner join tblProjects
                                           on tblEmployees.ProjectID = tblProjects.ProjectID inner join tblProjectManager
                                           on tblProjects.ProjectID=tblProjectManager.ProjectID inner join tblEvaluation
                                           on tblEmployees.EmployeeId=tblEvaluation.EmpId
                                           where tblCode.CodeName='"+code.CodeName+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                SingleEmployeEvaluationReport evaluation = new SingleEmployeEvaluationReport();
                while (reader.Read())
                {
                    evaluation.EmpName = reader[0].ToString();
                    evaluation.Designation = reader[1].ToString();
                    evaluation.Project = reader[2].ToString();
                    evaluation.Pm = reader[3].ToString();

                    DateTime dt = Convert.ToDateTime(reader[4]);
                    evaluation.Date = dt.ToShortDateString();

                    evaluation.Skill = reader[5].ToString();
                    evaluation.Communication = reader[6].ToString();
                    evaluation.Punctuality = reader[7].ToString();
                    evaluation.Comments = reader[8].ToString();
                   
                }
                return evaluation;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<SingleEmployeEvaluationReport> GetEvaluationReport(Project project)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select tblEmployees.Name,tblDesignations.Title,tblProjects.ProjectName,tblProjectManager.EmpId as manager,
                                           tblEvaluation.date,tblEvaluation.Skill,tblEvaluation.Communication,tblEvaluation.Punctuality,tblEvaluation.Comments
                                           
                                           from tblCode inner JOIN tblEmployees
                                           on tblCode.Id = tblEmployees.Code_CodeID inner JOIN tblDesignations
                                           on tblEmployees.DesignationID=tblDesignations.Id inner join tblProjects
                                           on tblEmployees.ProjectID = tblProjects.ProjectID inner join tblProjectManager
                                           on tblProjects.ProjectID=tblProjectManager.ProjectID inner join tblEvaluation
                                           on tblEmployees.EmployeeId=tblEvaluation.EmpId
                                           where tblProjects.ProjectID='"+project.Id+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<SingleEmployeEvaluationReport> reports = new List<SingleEmployeEvaluationReport>();
                while (reader.Read())
                {
                    SingleEmployeEvaluationReport evaluation = new SingleEmployeEvaluationReport();
                    evaluation.EmpName = reader[0].ToString();
                    evaluation.Designation = reader[1].ToString();
                    evaluation.Project = reader[2].ToString();
                    evaluation.Pm = reader[3].ToString();

                    DateTime dt = Convert.ToDateTime(reader[4]);
                    evaluation.Date = dt.ToShortDateString();

                    evaluation.Skill = reader[5].ToString();
                    evaluation.Communication = reader[6].ToString();
                    evaluation.Punctuality = reader[7].ToString();
                    evaluation.Comments = reader[8].ToString();
                    reports.Add(evaluation);
                }
                return reports;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}